import pymysql
import random
from datetime import datetime
from faker import Faker
faker1 = Faker(locale='zh_cn')
# 1、通过python连接pymysql数据库，封装类方法
# 1.1、随机插入100条数据到数据库表，包括姓名、年龄、家庭住址、身份证号码
# db = pymysql.connect(host="121.40.86.4",user='root',password='Aa010903!',database='test',cursorclass=pymysql.cursors.DictCursor)
# cursor = db.cursor()
# 插入随机的100条数据
"""for i in range(100):
    name = faker1.name()
    address = faker1.address()
    phone = faker1.ssn()
    age = str(int(datetime.now().strftime('%Y%m%d')) - int(phone[-12:-4]))[0:2]
    cursor.execute(f'insert into people(name,age,address,phone_number) values("{name}",{age},"{address}","{phone}")')
db.commit()
with open("log.txt",'a',encoding='utf-8') as f:
    f.write(f'{datetime.now().strftime("%Y-%m-%D %H:%M:%S")}---随机插入100条数据\n')"""
# db.close()
# 1.2、查询全部的数据
"""sql = "select *from people"
cursor.execute(sql)
print(cursor.fetchall())
with open("log.txt",'a',encoding='utf-8') as f:
    f.write(f'{datetime.now().strftime("%Y-%m-%D %H:%M:%S")}---查询全部数据\n')"""
# db.close()
# 1.3、删除指定一条数据
"""cursor.execute('select *from people where name="罗欢"')
with open("log.txt",'a',encoding='utf-8') as f:
    f.write(f'{datetime.now().strftime("%Y-%m-%D %H:%M:%S")}---删除数据{cursor.fetchone()}\n')
sql = 'delete from people where name="罗欢"'
cursor.execute(sql)
db.commit()

print(cursor.fetchall())"""
# db.close()
# 1.4、封装批量删除方法，可以删除多条
"""def delete_sql(name_list):
    for i in name_list:
        cursor.execute(f'select *from people where name="{i}"')
        with open("log.txt", 'a', encoding='utf-8') as f:
            f.write(f'{datetime.now().strftime("%Y-%m-%D %H:%M:%S")}---删除数据{cursor.fetchone()}\n')
        cursor.execute(f'delete from people where name="{i}"')
    db.commit()
    db.close()
delete_sql(['廖峰','夏淑英'])"""

# 1.5、可以指定增加单独一条数据到表里
"""cursor.execute('insert into people(name,age,address,phone_number) values("黄勃",60,"辽宁省沈阳市皇姑区1793号","213409196523451235")')
db.commit()
sql='select *from people where name="黄渤"'  
cursor.execute(sql)
with open("log.txt",'a',encoding='utf-8') as f:
    f.write(f'{datetime.now().strftime("%Y-%m-%D %H:%M:%S")}---插入数据{cursor.fetchone()}\n')  # 没有数据返回
cursor.close()
db.close()"""
# 以上每个操作步骤要写日志信息到log.txt里

# 2、做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法
# db = pymysql.connect(host='121.40.86.4',user='root',password='Aa010903!',database='test',cursorclass=pymysql.cursors.DictCursor)
# cursor = db.cursor()
# 存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
"""try:
    name = input('请输入学生姓名：')
    age = input('请输入学生年龄：')
    address = input('请输入学生地址：')
    phone = input('请输入学生手机号：')
    sql = f'insert into student values("{name}",{age},"{address}","{phone}")'
    cursor.execute(sql)
    db.commit()
    print(f"{name}存入成功！")
except Exception as e:
    print(e)
    print("存入失败!")
finally:
    db.close()"""
# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
"""name = input("请输入要修改的学生姓名：")
sql = f'select *from student where name="{name}"'
cursor.execute(sql)
if cursor.fetchone()!=None:
    print("姓名\t年龄\t地址\t手机号")
    user_in = input("请输入要修改的字段：")
    if user_in=='姓名':
        new_name = input("请输入修改后的名字：")
        update = f'update student set name="{new_name}" where name = "{name}"'
        cursor.execute(update)
    elif user_in=='年龄':
        new_age = input("请输入修改后的年龄：")
        update = f'update student set age={new_age} where name = "{name}"'
        cursor.execute(update)
    elif user_in == '地址':
        new_address = input("请输入修改后的地址：")
        update = f'update student set address="{new_address}" where name ="{name}"'
        cursor.execute(update)
    elif user_in=='手机号':
        new_phone = input("请输入修改后的手机号：")
        update = f'update student set phone="{new_phone}" where name = "{name}"'
        cursor.execute(update)
    else:
        print("字段名错误请重新选择")
    db.commit()
    db.close()
    print("修改成功！")
else:
    print("您输入的学生不存在")"""
# 删除学生：删除的学生将不在展示
"""name = input("请输入要删除的学生姓名：")
cursor.execute('select name from student')
list_name = cursor.fetchall()
for i in range(len(list_name)):
    if list_name[i]['name']==name:
        sql = f'delete from student where name="{name}"'
        cursor.execute(sql)
        db.commit()
        db.close()
        print("删除成功")"""

# 展示全部学生：可以看到全部的学生姓名
"""sql = 'select * from student'
cursor.execute(sql)
print(cursor.fetchall())
db.close()"""
# 学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
"""sql = 'select name from student'
cursor.execute(sql)
dict1 = cursor.fetchall()
i = random.randint(0,len(dict1)-1)
print(dict1[i]['name']+"到！")
"""
# 以上内容，需要连接数据库，每次的数据都需要增、删、改、查去做
#
# 3、类作业，统计镰刀和斧头的数量，要使用类方法
"""class Scythe:
    def __init__(self,num):
        self.num = num
    def get_num(self):
        return self.num
class Axe:
    def __init__(self,num):
        self.num = num
    def get_num(self):
        return self.num
if __name__ == '__main__':
    s  =Scythe(12)
    a = Axe(15)
    sum = s.get_num()+a.get_num()
    print(sum)"""
# 4、新建学生成绩管理系统，随机生成100个 学生信息插入到数据库：
    # 学生信息组成：学号、学生姓名、英语成绩、数学成绩、语文成绩、班级号（班级号有：1809A，1809B，1810A，1810B）
db = pymysql.connect(host='121.40.86.4',user='root',password='Aa010903!',database='test',cursorclass=pymysql.cursors.DictCursor)
cursor = db.cursor()
# 成绩划分为：优秀，良好，中等，及格，不及格。良好比中等好。
# 优秀：90—100之间（包括90）
# 良好：80-90
# 中等：70-80
# 及格：60-70
# 不合格：分数低于60分。

# 成绩管理系统提供以下方法：
# 《1》 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
"""eng_1 = 'select name,class_id from s_score where eng_score>=90'
cursor.execute(eng_1)
print("英语成绩优秀")
print(cursor.fetchall())
eng_2 = 'select name,class_id from s_score where eng_score >=80 and eng_score<90'
cursor.execute(eng_2)
print("英语成绩良好")
print(cursor.fetchall())
eng_3 = 'select name,class_id from s_score where eng_score >=70 and eng_score<80'
cursor.execute(eng_3)
print("英语成绩中等")
print(cursor.fetchall())
eng_4 = 'select name,class_id from s_score where eng_score >=60 and eng_score<70'
cursor.execute(eng_4)
print("英语成绩及格")
print(cursor.fetchall())
eng_5 = 'select name,class_id from s_score where eng_score<60'
cursor.execute(eng_5)
print("英语成绩不及格")
print(cursor.fetchall())
cursor.close()
db.close()"""
# 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
# sql_2 = 'select class_id,math_score from s_score order by class_id, math_score desc'
"""sql_2 = 'select math_score from s_score order by class_id, math_score desc'     # 先按照班级排序再按照成绩排序
sql_02 = 'select math_score from s_score order by math_score desc'  # 直接按照成绩降序排列
cursor.execute(sql_2)
print(cursor.fetchall())
cursor.close()
db.close()"""
# 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
"""sql_3 = 'select class_id,count(*) as "人数" from s_score group by class_id order by count(*) desc'
cursor.execute(sql_3)
print(cursor.fetchall())
cursor.close()
db.close()"""
# 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
"""sql_4 = 'select name,class_id from s_score where eng_score<60 or math_score<60 or chinese_score<60 group by class_id,name'
cursor.execute(sql_4)
print(cursor.fetchall())
cursor.close()
db.close()"""
# 5、定义一个图书管理系统类，图书可以新增、修改、删除，要求新增、修改、删除的图书，都要出现在数据库中
# 新增：图书名、作者名称、新建年月日
for i in range(5):
    b_name = faker1.bank()
    z_name = faker1.name()
    date1 = '2020-08-19'
    cursor.execute(f'insert into book values("{b_name}","{z_name}","{date1}")')
db.commit()
db.close()
# 修改：图书名、作者名称、新建年月日
name = input("请输入要修改的图书名称：")
sql = f'select *from book where book_name="{name}"'
cursor.execute(sql)
if cursor.fetchone()!=None:
    print("图书名\t作者名\t新建年月日")
    user_in = input("请输入要修改的字段：")
    if user_in=='图书名':
        new_name = input("请输入修改后的图书名：")
        update = f'update student set book_name="{new_name}" where book_name = "{name}"'
        cursor.execute(update)
    elif user_in=='作者名':
        new_name1 = input("请输入修改后的作者名：")
        update = f'update student set writer_name={new_name1} where book_name = "{name}"'
        cursor.execute(update)
    elif user_in == '新建年月日':
        new_c_time= input("请输入修改后的新建年月日：")
        update = f'update student set c_time="{new_c_time}" where book_name ="{name}"'
        cursor.execute(update)
    else:
        print("字段名错误请重新选择")
    db.commit()
    cursor.close()
    db.close()
    print("修改成功！")
else:
    print("您输入的学生不存在")
# 删除：图书删掉，且不在展示
d_name = input("请输入要删除的图书姓名：")
sql = f'delete from book where book_name="{d_name}"'
cursor.execute(sql)
db.commit()
cursor.close()
db.close()
# 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果
# 全量查看
cursor.execute('select * from book')

name_in = input("请输入要查找的图书名：")
# 精确查询
cursor.execute(f'select *from book where book_name ="{name_in}"')
# 模糊查询
cursor.execute(f'select *from book where book_name like "%{name_in}%"')
cursor.close()
db.close()